#coding: utf8
import re
from pygeocoder import Geocoder
from scrapy import log
import time
import MySQLdb
import re, htmlentitydefs
import os.path 
from ConfigParser import SafeConfigParser

class GoogleAddressAttr(object):
    location=None
    lat=None
    long=None
    address_format=None
    country_3=None
    admin_area_L1=None
    admin_area_L2=None
    locality=None
    zip=None

class DatabaseDetails(object):
    mode = ""
    host = ""
    user = ""
    passw = ""
    skema = ""
    ctModeLocal = 'db_local'
    ctModeRemote = 'db_remote'

    #file = "C:/BUSINESS/fresh-buzzing/python/fresh/fresh/iRestauranting.properties"
    #class called from scrapy framework but also directly from python, different way to relatively refer to a file. in future pass file parameter in
    file = "./fresh/iRestauranting.properties"
    file2 = "./iRestauranting.properties"
    
    def __init__(self):
        try:
            parser = SafeConfigParser()
            
            if os.path.isfile(self.file) == True:
                parser.read(self.file)
            elif os.path.isfile(self.file2) == True:
                parser.read(self.file2)
            else:
                log.msg("Error no se encuetra fichero de configuracion de base de dats " + file,level=log.ERROR)
                return
                
            """            
            for section_name in parser.sections():
                print 'Section:', section_name
                print '  Options:', parser.options(section_name)
                for name, value in parser.items(section_name):
                    print '  %s = %s' % (name, value)
                print
            print '11111111111111111111111end sections'
            """
            
           

            self.mode = parser.get('db','mode')
            self.mode = str(self.mode).strip()
            self.host = parser.get(self.mode,'host')
            self.user = parser.get(self.mode,'user')
            self.passw = parser.get(self.mode,'passw')
            self.skema = parser.get(self.mode,'skema')
            print "mode: " + str(self.mode) + "host: " + str(self.host) + "user: " + str(self.user) + "passw: " + str(self.passw) + "skema: " + str(self.skema)
            
        except Exception,e:
            log.msg("Error en leyendo propiedades fichero: " + file + str(e) ,level=log.ERROR)
        
  
class Utils(object):

    GOOGLE_ROOFTOP='ROOFTOP'
    DEBUG=log.DEBUG
    ERROR=log.ERROR
    INFO=log.INFO
    WARNING=log.WARNING

    dictLOG = {log.DEBUG: "DEBUG",log.ERROR: "ERROR",log.INFO:"INFO",log.WARNING:"WARNING"}

    TXT_DEBUG='DEBUG'
    TXT_ERROR='ERROR'
    TXT_INFO='INFO'
    TXT_WARNING='WARNING'
    
    db=None

    logProcessName=''
    logProcessId=0
    logError=False

    logLocalName='UTILS'
    logMasterProcessName='SCRAPY'

    ROBOT_YELP='YELP'
    ROBOT_SALIR='SALIR'
    ROBOT_RESTALO='RESTALO'
    ROBOT_ATRAPALO='ATRAPALO'
    ROBOT_TRIP='TRIPADVISOR'
    ROBOT_TENEDOR='TENEDOR'

    IND_DB_YES='Y'
    IND_DB_NO='N'

    NA='NA'
    
    
    def __init__(self):
        self.__connectDB()    

    def convertNullToNA(self,data):
        if data is None:
            return self.NA
        else:
            return data

    def scrapyQA(self,restaurant):
        try:

            total = self.convertListToString(restaurant['reviews_total'])
            if total is None or len(total)==0:
                total='0'
            itotal = int(total)
            if len(restaurant['reviews']) >0 and itotal==0:
                 self.logProcessDetail("Hay reviews pero el total es 0, revisar scrapy para restaurante: "+str(restaurant),self.ERROR)
                 return
            if len(restaurant['reviews']) ==0 and itotal>0:
                self.logProcessDetail("No hay reviews pero el total es mayor que 0, revisar scrapy para restaurante: "+str(restaurant),self.ERROR)
                return



            for r in restaurant['reviews']:
                if r.get('comment') is None and r.get('comment_positive') is None and r.get('comment_negative') is None:
                    self.logProcessDetail("No hay descripcion alguna en el post, revisar scrapy para restaurante: "+str(restaurant),self.ERROR)
                if r.get('author') is None or len(r.get('author'))==0:
                    self.logProcessDetail("No hay autor de comentario, posible error de scrapy para restaurante: "+str(restaurant),self.ERROR)
        
        except Exception,e:
            self.logProcessDetail("Error en scrapyQA:  " + str(e) + "restaurant: " + str(restaurant),self.ERROR)


    """gets the id of the type of restaurant from master, in case description is not in, it inserts it"""
    def getMasterTypesRestSource(self,robot,lTypes):
        lTypesIds = []
        
        try:
            for rest_type in lTypes:
                sql = "SELECT ID_TYPE_REST_SOURCE from MAST_TYPE_REST_SOURCE where ID_WEB_REVIEW=%s and DS_TYPE_REST_SOURCE=%s"
                self.__getCursor()
                self.cursor.execute(sql,(robot,rest_type ))
                
                data=self.cursor.fetchone()
                if data is not None:
                    lTypesIds.append(data[0])
                else:
                    sql = "INSERT INTO MAST_TYPE_REST_SOURCE (ID_WEB_REVIEW, DS_TYPE_REST_SOURCE) values (%s,%s)"
                    self.cursor.execute(sql,(robot,rest_type))
                    self.cursor.execute('SELECT LAST_INSERT_ID()')
                    data=self.cursor.fetchone()
                    lTypesIds.append(data[0])
        except Exception,e:
            self.logProcessDetail("Error en insertMasterRestType:  " + str(e),self.ERROR)
        else:
            self.db.commit()
        finally:
            self.cursor.close()

        return lTypesIds
        
    def logProcessInit(self,filename):

        log.start(logfile=filename,loglevel=log.DEBUG)
        #filename=None
        


    def logProcessBegin(self,name):
        ret = -1
        try:
            sql = "INSERT INTO LOGS_PROCESS (DT_PROCESS_INI, DS_PROCESS_NAME,DS_MASTER_PROCESS_NAME) VALUES (NOW(),%s,%s)"
            self.__getCursor()
            self.cursor.execute(sql,(str(name),self.logMasterProcessName ))

            self.cursor.execute('SELECT LAST_INSERT_ID()')
            data=self.cursor.fetchone();
            ret = data[0]
            self.logProcessId=ret
            self.logProcessName=name
        except Exception,e:
            log.msg("Error en logProcessDetail:  " + str(e),level=log.ERROR)
        else:
            self.db.commit()
        finally:
            self.logError=False
            self.cursor.close()

        return ret

    
    def logProcessEnd(self,text,type):
        """always log to file"""
        log.msg("Process: " + self.logProcessName + " " + text,level=type)
        """error if it is specifically said or it happened an error during execution"""
        if type == self.ERROR or self.logError:
            sOk='NOK'
        else:
            sOk='OK'
        
        try:
            sql = "UPDATE LOGS_PROCESS SET DT_PROCESS_END=NOW(), DS_RESULT_PROCESS=%s,BL_OK=%s WHERE ID_PROCESS=%s"
            try:
                self.__getCursor()
            except Exception,e:
                log.msg("Error en logProcessEnd, try to reconnect:  " + str(e),level=log.ERROR)
                self.__connectDB()
                self.__getCursor()
                
            self.cursor.execute(sql,(str(text),sOk,str(self.logProcessId)))

        except Exception,e:
            log.msg("Error en logProcessEnd:  " + str(e),level=log.ERROR)
        else:
            self.db.commit()
        finally:
            self.cursor.close()
            self.logProcessName=''
            self.logProcessId=0
            self.logError=False


    def logProcessDetail(self, message,type):
        """always log to file"""
        log.msg("Process: " + self.logProcessName + " " + message,level=type)
        if type == self.ERROR:
            self.logError=True
        try:
            sql = "INSERT INTO LOGS_DETAILS_PROCESS (ID_PROCESS, DT_EVENT, DS_MASTER_PROCESS_NAME, DS_PROCESS_NAME,DS_LOG_TYPE,DS_EVENT_TEXT) VALUES (%s, NOW(),%s,%s,%s,%s)"
            self.__getCursor()
            self.cursor.execute(sql,(str(self.logProcessId),self.logMasterProcessName,self.logProcessName,self.dictLOG.get(type),str(message)))
        except Exception,e:
            log.msg("Error en logProcessDetail:  " + str(e),level=log.ERROR)
        else:
            self.db.commit()
        finally:
            self.cursor.close()


    def log(self,name,message,type):
        """always log to file"""
        log.msg("Process: " + self.logProcessName + " " + message,level=log.DEBUG)
        """just log to DB if an error occurred"""
        if type==self.ERROR:
            self.logError=True
        if type==self.ERROR or type==self.WARNING or type==self.INFO:
            if (len(self.logProcessName)>0):
                """if there's a processname means that logProcessBegin was called so we have to log into detail"""
                self.logProcessDetail(message,type)
            else:
                """no logProcessBegin was called so do it all at once""" 
                idlog = self.logProcessBegin(name)
                if (idlog != -1):
                    self.logProcessEnd(message,type)
            

    def getDomain(self,url):
        try:
            url = url.replace('http://www.','')
            url = url.replace('http://','')
            po = url.find('/')
            if po>0:
                url = url[0:po]
                
            return url

        except Exception,e:
            self.log(self.logLocalName, "Error en getDomain:  " + str(e),self.ERROR)
        

    
    def convertListToString(self,list):
        try:
            r = ""
            if list is not None:
                for x in list:
                    r = r + x
                return r.strip()
            else:
                return None
        except Exception,e:
            self.log(self.logLocalName, "Error en convertir ListToString:  " + str(e) + list,self.ERROR)

    def __getGoogleAddressComponent(self,geo,type):
        try:
            for i in geo[0].current['address_components']:
                if type in i['types']:
                    return i['long_name']
        except Exception,e:
            self.log(self.logLocalName, "Error en __getGoogleAddressC:  " + str(e),self.WARNING)


    def __getINECodesFromZIP(self,attr):
        try:
            sql = "SELECT count(*) FROM MAST_ZIP_CODES WHERE ID_ZIP_CODE=%s and ID_LOCALITY is not null"
            self.__getCursor()
            self.cursor.execute(sql,(str(attr.zip)))
            data=self.cursor.fetchone();
            #that shouldn't be like that but since design is wrong we take the first locality with that CP
            if data[0] is None or int(data[0])==0:
                self.log(self.logLocalName, "Error en diccionario MAST_ZIP_CODES para codigo: " + str(attr.zip),self.ERROR)
                return
            else:
                sql = "SELECT ID_COUNTRY_3, ID_ADMIN_AREA_L1, ID_ADMIN_AREA_L2, ID_LOCALITY FROM MAST_ZIP_CODES WHERE ID_ZIP_CODE=%s and ID_LOCALITY is not null LIMIT 1"
                self.cursor.execute(sql,(str(attr.zip)))
                data=self.cursor.fetchone();
                attr.country_3=data[0]
                attr.admin_area_L1=data[1]
                attr.admin_area_L2=data[2]
                attr.locality=data[3]
        except Exception,e:
            self.log(self.logLocalName, "Error en __getINECodesFromZIP:  " + str(e),self.WARNING)  
        else:
            self.db.commit()
        finally:
            self.cursor.close()            
        
            
    def getGoogleCoordinates(self,address,cp,attr):
        try:
            address = address + " ," + cp + " ,España"
            geo = Geocoder.geocode(address)
            attr.lat = geo[0].coordinates[0]
            attr.long = geo[0].coordinates[1]
            attr.location = geo[0].current['geometry']['location_type']
            format_address = geo[0].current['formatted_address']
            attr.address_format = format_address.replace("Spain","España")
            attr.zip = self.__getGoogleAddressComponent(geo,'postal_code')

            #get the rest of the political division from Spanish INE dictionary
            if attr.zip is not None:
                self.__getINECodesFromZIP(attr)
            else:
                self.log(self.logLocalName, "No se obtiene codigo postal en google por direccion: " + str(address) ,self.WARNING)            

            #attr.country_3 = self.convertListToString(self.__getGoogleAddressComponent(geo,'country'))          
            #attr.admin_area_L1 = self.__getGoogleAddressComponent(geo,'administrative_area_level_1')
            #attr.admin_area_L2 = self.__getGoogleAddressComponent(geo,'administrative_area_level_2')
            #attr.locality = self.__getGoogleAddressComponent(geo,'locality')
            
            

            log.msg( "lat" + str(attr.lat),level=log.DEBUG)
            log.msg( "long" + str(attr.long),level=log.DEBUG)
            log.msg( "ZIP" + str(attr.zip),level=log.DEBUG)
            time.sleep( 2 )
            return 
        except Exception,e:
            self.log(self.logLocalName, "Error en getting google coordinates:  " + str(e),self.WARNING)
                    
    
    def getTwitterAccountFromList(self,twList):
        ret=None
        l=dict()
        try:
            for tw in twList: 
                log.msg("tw inicial: " + str(tw),level=log.DEBUG)
                if len(str(tw).strip()) ==0:
                    continue
                po = tw.find("share")
                if po != -1:
                    continue

                po = tw.find("status")
                if po != -1:
                    continue
                
                po = tw.rfind("/")
                if po>0:
                    """the first / is from http, it means there's no account probably just http://twitter...."""
                    if tw[:po] == 'http:/' or tw[:po] == 'https:/':
                        continue
                    aux=tw[po+1:].rstrip()
                    aux = aux.replace("%40","@")
                    if len(aux)==0:
                        continue
                    l[aux.lower().rstrip()]=0
                    
            if len(l.keys())>1:
                self.log(self.logLocalName, "TOO MANY ACCOUNTS OF TWITTER:  " + str(l.keys()),self.INFO)
            elif len(l.keys())==1:
                ret = l.keys()[0]

            return ret

        except Exception,e:
            self.log(self.logLocalName, "Error when getting twitter: " + str(e) + "tw: " + str(twList),self.ERROR)
    
    def getFaceBookAccountFromList(self,fbList):

        ret=None
        l=dict()
        try:
            for fb in fbList:
                """self.log(self.logLocalName, "checking FB: " + str(fb), self.DEBUG)"""
                if len(str(fb).strip()) ==0:
                    continue
                po = fb.find("share.php")
                if po != -1:
                    continue

                po = fb.find("sharer.php")
                if po != -1:
                    continue

                po = fb.find("events.php")
                if po != -1:
                    continue

                po = fb.find("event.php")
                if po != -1:
                    continue

                po = fb.find("editevent.php")
                if po != -1:
                    continue
                
                po = fb.find("apps.facebook.com")
                if po != -1:
                    continue

                po = fb.find("photo.php")
                if po != -1:
                    continue

                po = fb.find("/media/")
                if po != -1:
                    continue



                """
                po = fb.rfind("profile.php?id=")
                if po>0:
                    aux = fb[po+15:].rstrip()
                    po = aux.find("&")
                    if po>0:
                        aux=aux[:po].rstrip()
                    l[aux]=0
                    continue
                """

                po = fb.rfind("profile.php")
                if po>0:
                    aux = fb[po+11:].rstrip()
                    po = aux.rfind("id=")
                    if po>0:
                        aux=aux[po+3:].rstrip()
                        po = aux.find("&")
                        if po>0:
                            aux=aux[:po].rstrip()
                        l[aux]=0
                    continue

                """self.log(self.logLocalName, "outside group: " + str(fb), self.DEBUG)"""
                po = fb.rfind("group.php?gid=")
                if po>0:
                    """self.log(self.logLocalName, "inside group: " + str(fb), self.DEBUG)"""
                    aux = fb[po+14:].rstrip()
                    po = aux.find("&")
                    if po>0:
                        aux=aux[:po].rstrip()
                    l[aux]=0
                    continue

                po = fb.rfind("home.php?sk=")
                if po>0:
                    """self.log(self.logLocalName, "inside group: " + str(fb), self.DEBUG)"""
                    aux = fb[po+12:].rstrip()
                    po = aux.find("&")
                    if po>0:
                        aux=aux[:po].rstrip()
                    l[aux]=0
                    continue

                
                po = fb.rfind("/")
                if po>0:
                    log.msg("po1: " + str(po),level=log.DEBUG)
                    """the first / is from http, it means there's no account probably just http://facebook...."""
                    if fb[:po] == 'http:/' or fb[:po] == 'https:/':
                        continue

                    aux = fb[po+1:].rstrip()
                    log.msg("ret2: " + aux,level=log.DEBUG)
                    po = aux.rfind("?")
                    if po>0:
                        log.msg("po2: " + str(po),level=log.DEBUG)
                        aux=aux[0:po]
                        log.msg("ret3: " + aux,level=log.DEBUG)
                    if len(aux)>0:
                        l[aux]=0                        
                        
                
            if len(l.keys())>1:
                self.log(self.logLocalName, "TOO MANY ACCOUNTS OF FACEBOOK:  " + str(l.keys()),self.INFO)
            elif len(l.keys())==1:
                ret = l.keys()[0]
                
            return ret

        except Exception,e:
            self.log(self.logLocalName, "Error when getting fb: " + str(e) + "tw: " + str(fbList),self.ERROR)
            
    
    def getPhone(self,phone):
        try:
            phone = self.convertListToString(phone)
            tmp_p=""
            if len(phone)>0:
                """ - could mean also multiple phones """
                if phone.count('-') > 1:
                    phone = phone.replace('-','')
                    
                phone = phone.replace(' ','')
                phone = phone.replace('0034','')
                phone = phone.replace('+0034','')
                phone = phone.replace('(0034)','')
                phone = phone.replace('(+34)','')
                phone = phone.replace('+34','')
                phone = phone.replace('(+0034)','')
                phone = phone.replace('+(34)','')
                phone = phone.replace('(34)','')
                phone = phone.replace('(0)','')
                phone = phone.replace('93-','93')
                phone = phone.replace('93/','93')
                phone = phone.replace('*','')
                phone = phone.replace('‎','')
                
                phone = phone.strip()
                if phone.startswith('34'):
                    phone = phone[2:]
                phone = phone.replace('.','')
                phone = phone.replace('(93)','93')
                phone = phone.strip()
                if phone.startswith('-'):
                    phone = phone[1:]

                return phone.strip()
            else:
                return tmp_p
        except Exception,e:
            self.log(self.logLocalName, "Error when cleaning phone: " + str(e),self.ERROR)
                        
    
    def getRestAddressGoogleReady(self, restAddress):
        try:
            restAddress = restAddress.strip().lower()
            restAddress = restAddress.replace("d'","")
            restAddress = restAddress.replace("juan de borbón","joan de borbó")
            restAddress = restAddress.replace("enrique granados","enric granados")
            return restAddress
            
        except Exception,e:
            self.log(self.logLocalName, "Error when transforming restaurant adress google ready: " + str(e),self.ERROR)

    def unescape(self,url):
        return re.compile('%([0-9a-fA-F]{2})',re.M).sub(lambda m: chr(int(m.group(1),16)), url)

    def cleanString(self,str):
        if str is not None:
            str = str.strip().replace('\r\n'," ")
            str = str.replace('\n'," ")
            str = str.replace('\r'," ")
            str = str.replace('&nbsp'," ")
            bExit=False
            while (bExit==False):
                if str.find('   ')==-1:
                    bExit=True
                else:
                    str = str.replace('   '," ")
            str=str.strip()
        return str
        
                    

    def __getCursor(self):
        try:
            self.cursor = self.db.cursor()
            self.cursor.execute('SET NAMES utf8;')
            self.cursor.execute('SET CHARACTER SET utf8;')
            self.cursor.execute('SET character_set_connection=utf8;')
        except Exception,e:
            log.msg("Error al crear cursor MySQLdb:  " + str(e),level=log.ERROR)
            #reconnect
            self.__connectDB()
            
    

    def __connectDB(self):
        try:
            props = DatabaseDetails()
            self.db = MySQLdb.connect(props.host,props.user,props.passw,props.skema)
            self.db.set_character_set('utf8')
        except Exception,e:
            log.msg("-----------------Error al conectar a base de datos MySQLdb.connect: " + str(e),level=log.ERROR)
            return False
        return True


    def __del__(self):
        self.db.close()
        #super(Utils, self).__del__()

        